CREATE OR REPLACE VIEW view_adreses_union AS
SELECT 
    an.id AS adrese_id,
    al.name AS limenis,
    an.name AS nosaukums,
    ai.number AS pasta_indekss
FROM address_name an
LEFT JOIN address_level al ON an.id_address_level = al.id
LEFT JOIN address_index ai ON an.id_address_index = ai.id
WHERE al.name = 'valsts'

UNION ALL

SELECT 
    an.id AS adrese_id,
    al.name AS limenis,
    an.name AS nosaukums,
    ai.number AS pasta_indekss
FROM address_name an
LEFT JOIN address_level al ON an.id_address_level = al.id
LEFT JOIN address_index ai ON an.id_address_index = ai.id
WHERE al.name = 'novads'

UNION ALL

SELECT 
    an.id AS adrese_id,
    al.name AS limenis,
    an.name AS nosaukums,
    ai.number AS pasta_indekss
FROM address_name an
LEFT JOIN address_level al ON an.id_address_level = al.id
LEFT JOIN address_index ai ON an.id_address_index = ai.id
WHERE al.name = 'iela'

UNION ALL

SELECT 
    an.id AS adrese_id,
    al.name AS limenis,
    an.name AS nosaukums,
    ai.number AS pasta_indekss
FROM address_name an
LEFT JOIN address_level al ON an.id_address_level = al.id
LEFT JOIN address_index ai ON an.id_address_index = ai.id
WHERE al.name = 'eka';
